package insta.project.remote;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * BDDOpenHelper - Helper for database construction
 * 
 * @author FERRARI
 * 
 */

public class BDDOpenHelper extends SQLiteOpenHelper {
	public static final int VERSION_BDD = 4;
	public static final String NOM_BDD = "remote_music.db";
	public static final String TABLE_MUSIC = "music";
	public static final String COL_AUDIO_ID = "AUDIO_ID";
	public static final int _COL_AUDIO_ID = 0;
	public static final String COL_TITLE = "TITLE";
	public static final int _COL_TITLE = 1;
	public static final String COL_ARTIST = "ARTIST";
	public static final int _COL_ARTIST = 2;
	public static final String COL_FILENAME = "FILENAME";
	public static final int _COL_FILENAME = 3;
	public static final String COL_IMAGEURI = "IMAGEURI";
	public static final int _COL_IMAGEURI = 4;
	public static final String COL_DATEINSERT = "DATEINSERT";
	public static final int _COL_DATEINSERT = 5;

	private boolean bServiceUp = false;

	public boolean isServiceUp() {
		return bServiceUp;
	}

	private static final String CREATE_BDD = "CREATE TABLE " + TABLE_MUSIC
			+ " (" + COL_AUDIO_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ COL_TITLE + " TEXT NOT NULL, " + COL_ARTIST + " TEXT NOT NULL, "
			+ COL_FILENAME + " TEXT NOT NULL, " + COL_IMAGEURI + " TEXT, "
			+ COL_DATEINSERT + " TEXT);";

	public BDDOpenHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
		String strServiceStatus = "down";
		try {
			strServiceStatus = HttpRequest
					.GET("http://www.freebangers.com/androidquery/status");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.bServiceUp = (strServiceStatus.equals("up")) ? true : false;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.d("BDDOpenHelperCreate:onCreate", CREATE_BDD);
		db.execSQL(CREATE_BDD);
		this.load(db);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE " + TABLE_MUSIC + ";");
		onCreate(db);
	}

	public void updateTracks(SQLiteDatabase db) {
		db.execSQL("DROP TABLE " + TABLE_MUSIC + ";");
		onCreate(db);
	}

	/**
	 * Loads .csv file into the datbase
	 * 
	 * @param db
	 */
	public void load(SQLiteDatabase db) {
		try {
			/**
			 * Check if freebangers.com is up
			 */
			if (this.isServiceUp()) {
				long lgCurrentUpdate = System.currentTimeMillis();
				String[][] strRemoteMusicData = HttpRequest.getInstance().getMusicListArray();
				for (int i = 0; i < strRemoteMusicData.length; i++) {
					String[] musicData = strRemoteMusicData[i];
					if (musicData.length == 5 && musicData[0] != null && musicData[1] != null && musicData[2] != null && musicData[3] != null  && musicData[4] != null) {
						String request = "INSERT INTO " + TABLE_MUSIC + "("
								+ COL_AUDIO_ID + ", " + COL_TITLE + ", "
								+ COL_ARTIST + ", " + COL_FILENAME + ", "
								+ COL_IMAGEURI + " , " + COL_DATEINSERT
								+ ") VALUES (\"" + musicData[4] + "\", \""
								+ musicData[0] + "\", \"" + musicData[1]
								+ "\",\"" + musicData[2]
								+ "\",\"http://www.freebangers.com/__files/" + musicData[3]
								+ "\", " + lgCurrentUpdate + ")";
						Log.d("BDDOpenHelper.load", request);
						db.execSQL(request);
					}

				}
			}
		} catch (Exception e) {
			Log.d("java.exception@BDDOpenHelper.java", e.getMessage());

		}
	}
}
